/*
 * Created on Jun 9, 2005
 *
 */
package com.erclab.internal.xpresso.dataAccess;

import java.util.*;

import com.erclab.internal.xpresso.formelements.FormElement;
import com.erclab.internal.xpresso.forms.Form;
import com.erclab.internal.xpresso.forms.FormParameters;
import com.erclab.internal.xpresso.security.User;

/**
 * @author Enrique Rodriguez.
 *
 */
public class MySqlQueryBuilder extends QueryBuilder {

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#replaceParentKeyValue(java.lang.String)
	 */
	public String replaceParentKeyValue(String query, String parentKeyValue) {
		// TODO Auto-generated method stub
		return query;
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#replaceUniqueKeyValue(java.lang.String)
	 */
	public void replaceUniqueKeyValue(Query q, String uniqueKeyValue) {
		String query = q.getQuery();
		if (uniqueKeyValue != null){
			q.setQuery(query.replaceAll("-param:uniqueKeyValue-", uniqueKeyValue));
		} 
	}

	public void addSortByToQuery(Query q, String defaultOrder, String requestedOrder) {
		//Add sort order
		String query = q.getQuery();
		if (requestedOrder != null && !requestedOrder.equalsIgnoreCase("") && !requestedOrder.equalsIgnoreCase("null")){
			query += " ORDER BY " + requestedOrder;
		} else if (defaultOrder != null && !defaultOrder.equalsIgnoreCase("")) {
			query += " ORDER BY " + defaultOrder;
		}
		q.setQuery(query);
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#createFormElementQuery(com.erclab.internal.xpresso.formelements.FormElement, com.erclab.internal.xpresso.forms.FormParameters)
	 */
	public String createFormElementQuery(Form f, FormElement fe, FormParameters fp) {
		StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append(fe.getDestinationColumn());
		query.append(" FROM ");
		query.append(fe.getDestinationTable());
		query.append(" WHERE ");
		query.append(f.getUniqueKeyName());
		query.append("=");
		query.append(fp.getUniqueKeyValue());
		return query.toString();
	}
	
	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#modifyLookupQuery(java.lang.String, com.erclab.internal.xpresso.forms.FormParameters)
	 */
	public String modifyLookupQuery(String query, FormParameters parameters) {
		StringBuffer sb = new StringBuffer();
		if (parameters != null){
			if (parameters.getUniqueKeyValue() != null)
				query = query.replaceAll("-param:uniqueKeyValue-", parameters.getUniqueKeyValue());
			if (parameters.getParentKeyValue() != null)
				query = query.replaceAll("-param:parentKeyValue-", parameters.getParentKeyValue());
			
			query = query.replaceAll("-param:netId-", parameters.getUser().getNetId());
		}
		return query;
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#createUpdateQuery(java.util.ArrayList, java.lang.String, java.lang.String, java.lang.String)
	 */
	public Query createUpdateQuery(ArrayList attributes, String destinationObject, String uniqueKeyName, String uniqueKeyValue) {
		StringBuffer query = new StringBuffer();
		Query theQuery = new Query();
		String[] values = new String[attributes.size() + 1];
		
		query.append("UPDATE " + destinationObject + " SET ");
		for (int i = 0; i < attributes.size(); i++) {
			Attribute a = (Attribute)attributes.get(i);
			query.append(a.getName() + "=?");
			values[i] = a.getValues()[0];
			if(i+1 < attributes.size())
				query.append(", ");
		}
		query.append(" WHERE " + uniqueKeyName + " =?" );
		values[attributes.size()] = uniqueKeyValue;
		
		theQuery.setQuery(query.toString());
		theQuery.setValues(values);
		return theQuery;
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#createInsertQuery(java.util.ArrayList, java.lang.String, java.lang.String, java.lang.String)
	 */
	public Query createInsertQuery(
		ArrayList attributes,
		String destinationObject,
		String parentKeyName,
		String parentKeyValue) {

			StringBuffer query = new StringBuffer();
			Query theQuery = new Query();

			// Add an attribute if the parent key value is present
			if(validParentKeys(parentKeyName, parentKeyValue)){
				Attribute a = new Attribute();
				a.setName(parentKeyName);
				a.setValues(new String[]{parentKeyValue});
				attributes.add(a);
			}
			
			String[] values = new String[attributes.size()];
		
			query.append("INSERT INTO " + destinationObject + " (");
			for (int i = 0; i < attributes.size(); i++) {
				Attribute a = (Attribute)attributes.get(i);
				query.append(a.getName());
				values[i] = a.getValues()[0];
				if(i+1 < attributes.size()){
					query.append(", ");
				} else {
					query.append(") VALUES(");
				}
			}
			for (int i = 0; i < attributes.size(); i++) {
				if(i + 1 < attributes.size()){
					query.append("?, ");
				} else {
					query.append("?)");
				}
			}
		
			theQuery.setQuery(query.toString());
			theQuery.setValues(values);
			return theQuery;

	}

	/**
	 * @param parentKeyName
	 * @param parentKeyValue
	 * @return
	 */
	private boolean validParentKeys(String parentKeyName, String parentKeyValue) {
		return parentKeyName != null && !parentKeyName.equals("") && parentKeyValue != null && !parentKeyValue.equals(""); 
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#createDeleteQuery(java.lang.String, java.lang.String, java.lang.String)
	 */
	public Query createDeleteQuery(
		String destinationObject,
		String uniqueKeyName,
		String uniqueKeyValue) {
		
		Query theQuery = new Query();
		theQuery.setQuery("DELETE FROM " + destinationObject + " WHERE " + uniqueKeyName + "=?");
		theQuery.setValues(new String[]{uniqueKeyValue});

		return theQuery;
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#replaceUserInfo(com.erclab.internal.xpresso.dataAccess.Query, com.erclab.internal.xpresso.security.User)
	 */
	public void replaceUserInfo(Query q, User user) {
		String query = q.getQuery();
		if (user.getNetId() != null){
			q.setQuery(query.replaceAll("-param:netId-", "'" + user.getNetId() + "'"));
		} 
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#addPageNumber(com.erclab.internal.xpresso.dataAccess.Query, int)
	 */
	public void addPageNumber(Query q, int pageNumber, int recordsPerPage) {
		q.setQuery(q.getQuery() + " LIMIT " + (pageNumber-1)*recordsPerPage + ", " + recordsPerPage);
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.QueryBuilder#createMaxResultsQuery(com.erclab.internal.xpresso.dataAccess.Query)
	 */
	public Query createMaxResultsQuery(Query q) {
		Query maxQuery = new Query();
		maxQuery.setQuery("SELECT COUNT(1) FROM (" +  q.getQuery() + ") AS MAXRESULTSQUERY");
		maxQuery.setValues(q.getValues());
		return maxQuery;
	}

}
